学会这2招,轻松搞定数据透视表动态更新,效率猛增一倍!
Hello,大家好,我是最近在研究数据透视表的小爽~
最近,我收到了一个学员的求助:
简单归纳一下,这个问题就是:
如何能让数据透视表的数据源区域,自动进行扩选?
让数据能够自动扩展,确实非常复杂,要用到函数公式。
比如下面的函数方法~
以销售数据表为例,现在我们需要新增数据,让透视表实时更新。
PS:今天的文章需要掌握一点透视表基础,如果你想从零开始系统学习数据透视表,我推荐这门《数据透视表》网课!
01
利用 Offset 和 Counta 函数
我们先看看具体做法:
❶ 编写公式。
=OFFSET($A$1,,,COUNTA($A:$A),8)
▲左右滑动查看
❷ 定义名称管理器。
点击【公式】-【名称管理器】-「引用位置」。
粘贴上面的公式-名称为「数据源」。
=OFFSET(销售数据表!$A$1,,,COUNTA(销售数据表!$A:$A),8)
▲左右滑动查看
❸ 创建数据透视表。
插入数据透视表,表区域为上面的定义的名称——「数据源」。
❹ 实时更新。
这里我们用到两个函数——Offset 函数和 Counta 函数。
Counta 函数,能够计算非空单元格的个数;
COUNTA($A:$A)也就是计算 A 列中非空单元格的个数。
当我们在 A 列中往后添加数据,Counta 函数就会实时更新非空单元格的个数。
Offset 函数,能够偏移单元格。
基本语法如下:
=OFFSET(起始单元格,下移 N 行,右移 N 列,[扩展 N 行],[扩展 N 列])
就好比我去找小 E 玩,我从起点出发。
向下走 2 行,再向右走 2 列,就到了小 E 家,小 E 家在 3 行 4 列的位置上。
公式:
=OFFSET($A$1,,,COUNTA($A:$A),8)
▲左右滑动查看
案例中该公式的含义就是:
COUNTA 函数去获取 A 列非空单元格有 N 个。
OFFSET 函数收到 COUNTA 的数字后,
在 $A$1 单元格中直接扩选 N 行 8 列,8 是整个表的字段数。
到这里,大家应该知道这个公式的由来了吧~
不过我猜!
也可能有小伙伴会这样想:
这个函数公式好复杂啊,Offset 函数又是啥?我还是个连 Vlookup 函数都不会用的小!盆!友!哭料!
别担心,接下来我们就来见证「一个神奇的做法」~
如果你觉得你现在的能力,还不能拿下第 1 种方法,那请跳过直接看第 2 个方法。
第二种方法:这个表格,只要「穿上件衣服」,就能自动扩选区域,变身成为超人!
我们一起来看看吧~
02
智能表格
我们先来看看它的具体操作~
❶ 插入智能表。
点击【插入】选项卡-【表格】-勾选【表包含标题】-【确定】。
❷ 创建透视表。
点击【插入】选项卡-【数据透视表】。
刚刚我们的智能表的名称为「表 1」,所以创建数据源区域的时候,数据源区域为「表 1」。
❸ 动态更新数据源。
此时添加新数据,数据透视表右键刷新,就能实时更新数据源啦~
这里我们用到了智能表格,肯定会有小伙伴疑惑了:「智能表格究竟是怎么生成的?」
接下来,我们一一剖解它!
智能表格在哪?
普通表格区域,怎么才能变成智能表格?
这里介绍三种方法~
◆ 方法一:直接套用表格样式 ◆
点击【开始】选项卡-【套用表格样式】。
◆ 方法二:直接插入表格 ◆
这个也就是我们刚刚操作中所演示的方法~
全选表格,点击【插入】-【表格】。
◆ 方法三:快捷键法 ◆
转换为智能表格的快捷键有:【Ctrl+T】/【Ctrl+L】。
我们可以这样记忆:
【Ctrl+T】——【control+Table】——控制表格,也就是将普通表格变身为智能表!
【Ctrl+L】——【control+List】——控制列表,也就是将普通表格变身为智能表!
普通表格变身智能表格的做法,你 Get 到了吗~
智能表格还有一个神奇的功能就是扩展表格区域!
这就是表格的自动扩展属性。
数据透视表就是利用这一点,使数据源区域达到动态更新;
我们就是用扩展表格区域,来实时更新数据源的~
更详细的智能表格介绍,请戳👉神秘的智能表格到底「智能」在哪里?过来我就告诉你!
好啦,简单总结一下动态扩选区域的两种方法:
❶ 利用 Offset 和 Counta 函数——常用的动态扩选区域的公式套路;
❷ 智能表格——套上外衣,变身超人,可以自动扩选区域。
还有任何疑问的小伙伴,欢迎留言互动!
看完咱们小爽的文章,觉得有帮助,可以转评赞三连~笔芯~
如果你还想系统学习数据透视表,小 E 推荐这门《数据透视表》网课!
让你尽量少地使用函数公式,也能轻松完成各种各样的汇总统计!